<%@ Page language="vb" CodeFile="searchGarage.aspx.vb" Inherits="searchGarage_aspx_vb" %>

<%
    rowNumber = 1
    If (Request.QueryString("do")) = Nothing Then 
        subDo = Request.QueryString("subDo")
        If Len(garageID) > 0 Then
            garageID = CLng(garageID)
            dbOpen()
            garageID = Request.QueryString("garageID")
            GetRecSet()
            claimSum = IsNullChecking(claimSum)
            claimMomentSum = IsNullChecking(claimMomentSum)
            claimPartSum = IsNullChecking(claimPartSum)
            claimServiceSum = IsNullChecking(claimServiceSum)
            consumptionMaterialSum = IsNullChecking(consumptionMaterialSum)
            authorisedClaimMomentSum = IsNullChecking(authorisedClaimMomentSum)
            authorisedClaimPartSum = IsNullChecking(authorisedClaimPartSum)
            authorisedClaimServiceSum = IsNullChecking(authorisedClaimServiceSum)
            authorisedConsumptionMaterialSum = IsNullChecking(authorisedConsumptionMaterialSum)
            claimAmount = claimMomentSum + claimPartSum + claimServiceSum + consumptionMaterialSum
            authorisedClaimAmount = authorisedClaimMomentSum + authorisedClaimPartSum + authorisedClaimServiceSum + authorisedConsumptionMaterialSum
            If CInt(claimAmount) = 0 Or CInt(authorisedClaimAmount) = 0 Then
                authorisedPercentage = 0
            Else
                authorisedPercentage = CDbl(authorisedClaimAmount) / CDbl(claimAmount)
                authorisedPercentage = authorisedPercentage * 100
                RecSet.Close()
                RecSet = Nothing
                dbClose()
            End If
        End If
        '*************** OVER() gives the menu bar and the logo **************************************
        Over()
        '*********** Designing the Serch garage table ************
        Response.Write("<table cellpadding='0' cellspacing='0' border='0' width='1000'>")
        Response.Write("<tr>")
        Response.Write("<td valign='top'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
        Response.Write("<tr><td class='headline' colspan='3'>SEARCH GARAGE<hr class='hrHeadline'></td></tr>")
        Response.Write("<form action='searchGarage.aspx?subDo=search' method='post'>")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Country:</td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='countryID' id='countryID'>")
        Response.Write("<option value=''> Select country  ")
        dbOpen()
        '******* Calling the countries from the tblCountry in the database ******************
        RecSet = Conn.Execute("Select countryID, country From tblCountry")
        If Not RecSet.EOF Then
            Do Until RecSet.EOF
                countryID = RecSet.Fields("countryID").Value
                country = RecSet.Fields("country").Value
                Response.Write("<option value='" & countryID & "'> " & country & " ")
                RecSet.MoveNext()
            Loop
        End If
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("<option value=''> -------------------------------------- ")
        Response.Write("</select>")
        Response.Write("</td>")
        '*************** Designing the rest of the search garage table ***********************
        Response.Write("</tr>")
        Response.Write("<tr><td class='formBold'>Garage:</td><td align='right'><input type='text' name='garage' id='garage' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Garage ID:</td><td align='right'><input type='text' name='garageID' id='garageID' size='40' maxlength='50' onkeyup='re=/(\d*)/; re.exec(this.value); this.value=RegExp.$1;'></td></tr>")
        Response.Write("<tr><td class='formBold'>Invoice ID:</td><td align='right'><input type='text' name='garageInvoiceID' id='garageInvoiceID' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Street:</td><td align='right'><input type='text' name='street' id='street' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Zipcode:</td><td align='right'><input type='text' name='zipcode' id='zipcode' size='40' maxlength='50' onkeyup='re=/(\d*)/; re.exec(this.value); this.value=RegExp.$1;'></td></tr>")
        Response.Write("<tr><td class='formBold'>City:</td><td align='right'><input type='text' name='city' id='city' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Areacode:</td><td align='right'><input type='text' name='areacode' id='areacode' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Telephone:</td><td align='right'><input type='text' name='telephone' id='telephone' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Fax:</td><td align='right'><input type='text' name='fax' id='fax' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Email:</td><td align='right'><input type='text' name='email' id='email' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Note:</td><td align='right'><input type='text' name='note' id='note' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td align='right' colspan='2'><input type='submit' value='Search'></td></tr>")
        Response.Write("</form>")
        Response.Write("<tr><td colspan='3'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        '******************** The bellow table appears after clicking on EDIT GARAGE ( GARAGE STATISTICS ) **********************
        Response.Write("<td width='100'></td>")
        Response.Write("<td valign='top'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
        If Len(garageID) > 0 Then
            Response.Write("<tr><td class='headline' colspan='2'>GARAGE STATISTICS<hr class='hrHeadline'></td></tr>")
            Response.Write("<tr bgcolor='#eeeeee'><td>Number of claims reported:</td><td align='right'>" & claimCount & "</td></tr>")
            Response.Write("<tr><td>Total requested claim amount (" & countryCurrency & "):</td><td align='right'>" & FormatNumber(CDbl(claimAmount), 2) & "</td></tr>")
            Response.Write("<tr bgcolor='#eeeeee'><td>Total authorized claim amount (" & countryCurrency & "):</td><td align='right'>" & FormatNumber(CDbl(authorisedClaimAmount), 2) & "</td></tr>")
            Response.Write("<tr><td>Total authorized percentage (%):</td><td align='right'>" & FormatNumber(authorisedPercentage, 2) & "</td></tr>")
            Response.Write("<tr bgcolor='#eeeeee'><td>Total claim cost (" & countryCurrency & "):</td><td align='right'>" & FormatNumber(CDbl(claimSum), 2) & "</td></tr>")
            Response.Write("<tr><td colspan='2'><hr class='hrHeadline'></td></tr>")
        End If
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td><br></td></tr>")
        Response.Write("<tr>")
        Response.Write("<td valign='top' colspan='3'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='1000'>")
        '************* The belloow info will appear after clicking on SEARCH garage ************************
        '************* The info are taken from the form and assigned to variables ******************
        Response.Write("<tr><td class='headline' colspan='12'>SEARCH RESULT<hr class='hrHeadline'></td></tr>")
        countryID = Request.Form("countryID")
        garage = Request.Form("garage")
        garageID = Request.Form("garageID")
        garageInvoiceID = Request.Form("garageInvoiceID")
        street = Request.Form("street")
        zipcode = Request.Form("zipcode")
        city = Request.Form("city")
        areacode = Request.Form("areaCode")
        telephone = Request.Form("telephone")
        fax = Request.Form("fax")
        email = Request.Form("email")
        note = Request.Form("note")
        If Len(garageID) = 0 Then
            garageID = Request.QueryString("garageID")
        End If
        If Len(countryID) > 0 Then
            sqlCountryID = " And g.countryID = " & countryID & ""
        End If
        If Len(garage) > 0 Then
            garage = Trim(garage)
            garage = Replace(CStr(garage), "'", "")
            sqlGarage = " And g.garage Like '%" & garage & "%'"
        End If
        If Len(garageID) > 0 Then
            garageID = CLng(garageID)
            sqlGarageID = " And g.garageID = " & garageID & ""
        End If
        If Len(garageInvoiceID) > 0 Then
            garageInvoiceID = Trim(garageInvoiceID)
            garageInvoiceID = Replace(CStr(garageInvoiceID), "'", "")
            sqlGarageInvoiceID = " And cl.garageInvoiceID Like '%" & garageInvoiceID & "%'"
            sqlGarageInvoiceIDLeft = "("
            sqlGarageInvoiceIDRight = " INNER JOIN tblClaim cl ON cl.garageID = g.garageID)"
        End If
        If Len(street) > 0 Then
            street = Trim(street)
            street = Replace(street, "'", "")
            sqlStreet = " And g.street Like '%" & street & "%'"
        End If
        If Len(zipcode) > 0 Then
            sqlZipcode = " And g.zipcode Like '%" & zipcode & "%'"
        End If
        If Len(city) > 0 Then
            city = Trim(city)
            city = Replace(CStr(city), "'", "")
            sqlCity = " And g.city Like '%" & city & "%'"
        End If
        If Len(areacode) > 0 Then
            areacode = Trim(areacode)
            areacode = Replace(areacode, "'", "")
            sqlAreacode = " And g.areaCode Like '%" & areacode & "%'"
        End If
        If Len(telephone) > 0 Then
            telephone = Trim(telephone)
            telephone = Replace(telephone, "'", "")
            sqlTelephone = " And g.telephone Like '%" & telephone & "%'"
        End If
        If Len(fax) > 0 Then
            fax = Trim(fax)
            fax = Replace(fax, "'", "")
            sqlFax = " And g.fax Like '%" & fax & "%'"
        End If
        If Len(email) > 0 Then
            email = Trim(email)
            email = Replace(email, "'", "")
            sqlEmail = " And g.email Like '%" & email & "%'"
        End If
        If Len(note) > 0 Then
            note = Trim(note)
            note = Replace(CStr(note), "'", "")
            sqlNote = " And g.note Like '%" & note & "%'"
        End If
        If Len(subDo) = 0 Then
            sqlCountryID = " And g.countryID = 1000"
        End If
        dbOpen()
        RecSet = Conn.Execute("Select g.garageID, g.garage, g.city, g.note, c.country From " & sqlGarageInvoiceIDLeft & "(tblGarage g INNER JOIN tblCountry c ON c.countryID = g.countryID)" & sqlGarageInvoiceIDRight & " Where g.countryID > 0" & sqlCountryID & sqlGarage & sqlGarageID & sqlGarageInvoiceID & sqlStreet & sqlZipcode & sqlCity & sqlAreacode & sqlTelephone & sqlFax & sqlEmail & sqlNote & " Order By g.garage")
        Dim s As String
        
        s = "Select g.garageID, g.garage, g.city, g.note, c.country From " & sqlGarageInvoiceIDLeft & "(tblGarage g INNER JOIN tblCountry c ON c.countryID = g.countryID)" & sqlGarageInvoiceIDRight & " Where g.countryID > 0" & sqlCountryID & sqlGarage & sqlGarageID & sqlGarageInvoiceID & sqlStreet & sqlZipcode & sqlCity & sqlAreacode & sqlTelephone & sqlFax & sqlEmail & sqlNote & " Order By g.garage"
        If RecSet.EOF Then
            Response.Write("<tr><td><font color='red'>No customers found.</font></td></tr>")
        Else
            Response.Write("<tr bgcolor='#eeeeee'>")
            Response.Write("<td class='formBold'>Garage</td>")
            Response.Write("<td class='formBold'>Note</td>")
            Response.Write("<td class='formBold'>City</td>")
            Response.Write("<td class='formBold'>Country</td>")
            Response.Write("<td class='formBold'></td>")
            Response.Write("<td class='formBold'></td>")
            Response.Write("</tr>")
            Do Until RecSet.EOF
                garageID = RecSet.Fields("garageID").Value
                garage = RecSet.Fields("garage").Value
                city = RecSet.Fields("city").Value
                note = RecSet.Fields("note").Value
                country = RecSet.Fields("country").Value
                If rowNumber = 0 Then
                    strGrey = " bgcolor='#eeeeee'"
                    rowNumber = 1
                Else
                    strGrey = ""
                    rowNumber = 0
                End If
                Response.Write("<tr" & strGrey & ">")
                Response.Write("<td>" & garage & "</td>")
                Response.Write("<td>" & note & "</td>")
                Response.Write("<td>" & city & "</td>")
                Response.Write("<td align='right'>" & country & "</td>")
                Response.Write("<td><a href='editGarage.aspx?garageID=" & garageID & "'>edit garage</a></td>")
                Response.Write("<td><a href='searchGarage.aspx?subDo=viewClaims&garageID=" & garageID & "'>view claims</a></td>")
                Response.Write("</tr>")
                RecSet.MoveNext()
            Loop
        End If
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("<tr><td colspan='12'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td><br></td></tr>")
        '************** The Search claim table will appear after clicking on view cliems *********************
        If subDo = "viewClaims" Then
            Response.Write("<tr>")
            Response.Write("<td valign='top' colspan='3'>")
            Response.Write("<table cellpadding='2' cellspacing='2' width='1000'>")
            Response.Write("<tr><td class='headline' colspan='10'>SEARCH CLAIM<hr class='hrHeadline'></td></tr>")
            dbOpen()
            s = "Select c.vehicleID, c.claimStatus, c.garageInvoiceID, c.date, v.regnr, v.vinNumber, p.product, b.brand, m.model, co.countryCurrency, ((Select IsNull(Sum(cm.costPerHour*cm.numberOfHours),0) From tblClaimMoment cm Where cm.claimID = c.claimID)+(Select IsNull(Sum(cp.costPerPart*cp.numberOfParts),0) From tblClaimPart cp Where cp.claimID = c.claimID)+(Select IsNull(Sum(cs.costPerService*cs.numberOfService),0) From tblClaimService cs Where cs.claimID = c.claimID)+c.consumptionMaterialAmount) As requestedAmount, ((Select IsNull(Sum(cm.authorisedCostPerHour*cm.authorisedNumberOfHours),0) From tblClaimMoment cm Where cm.claimID = c.claimID)+(Select IsNull(Sum(cp.authorisedCostPerPart*cp.authorisedNumberOfParts),0) From tblClaimPart cp Where cp.claimID = c.claimID)+(Select IsNull(Sum(cs.authorisedCostPerService*cs.authorisedNumberOfService),0) From tblClaimService cs Where cs.claimID = c.claimID)+c.authorizedConsumptionMaterialAmount) As approvedAmount From (((((tblClaim c INNER JOIN tblVehicle v ON v.vehicleID = c.vehicleID) INNER JOIN tblProduct p ON p.productID = v.productID) INNER JOIN tblBrand b ON b.brandID = v.brandID) INNER JOIN tblModel m ON m.modelID = v.modelID) INNER JOIN tblCountry co ON co.countryID = c.countryID) Where c.garageID = " & garageID & ""
            RecSet = Conn.Execute("Select c.vehicleID, c.claimStatus, c.garageInvoiceID, c.date, v.regnr, v.vinNumber, p.product, b.brand, m.model, co.countryCurrency, ((Select IsNull(Sum(cm.costPerHour*cm.numberOfHours),0) From tblClaimMoment cm Where cm.claimID = c.claimID)+(Select IsNull(Sum(cp.costPerPart*cp.numberOfParts),0) From tblClaimPart cp Where cp.claimID = c.claimID)+(Select IsNull(Sum(cs.costPerService*cs.numberOfService),0) From tblClaimService cs Where cs.claimID = c.claimID)+c.consumptionMaterialAmount) As requestedAmount, ((Select IsNull(Sum(cm.authorisedCostPerHour*cm.authorisedNumberOfHours),0) From tblClaimMoment cm Where cm.claimID = c.claimID)+(Select IsNull(Sum(cp.authorisedCostPerPart*cp.authorisedNumberOfParts),0) From tblClaimPart cp Where cp.claimID = c.claimID)+(Select IsNull(Sum(cs.authorisedCostPerService*cs.authorisedNumberOfService),0) From tblClaimService cs Where cs.claimID = c.claimID)+c.authorizedConsumptionMaterialAmount) As approvedAmount From (((((tblClaim c INNER JOIN tblVehicle v ON v.vehicleID = c.vehicleID) INNER JOIN tblProduct p ON p.productID = v.productID) INNER JOIN tblBrand b ON b.brandID = v.brandID) INNER JOIN tblModel m ON m.modelID = v.modelID) INNER JOIN tblCountry co ON co.countryID = c.countryID) Where c.garageID = " & garageID & "")
            If RecSet.EOF Then
                '********* if no cliem found in the database So display NO CLIAM FOUND ************************
                Response.Write("<tr><td><font color='red'>No claims found</font></td></tr>")
            Else
                '*************** ELSE display the bellow info ***************
                Response.Write("<tr bgcolor='#eeeeee'>")
                Response.Write("<td class='formBold'>Claim status</td>")
                Response.Write("<td class='formBold'>Licence number</td>")
                Response.Write("<td class='formBold'>VIN-number</td>")
                Response.Write("<td class='formBold'>Car make</td>")
                Response.Write("<td class='formBold'>Product</td>")
                Response.Write("<td class='formBold'>Requested amount</td>")
                Response.Write("<td class='formBold'>Approved amount</td>")
                Response.Write("<td class='formBold'>Garage invoice ID</td>")
                Response.Write("<td class='formBold'>Date</td>")
                Response.Write("<td class='formBold'></td>")
                Response.Write("</tr>")
                rowNumber = 1
                Do Until RecSet.EOF
                    vehicleID = RecSet.Fields("vehicleID").Value
                    claimStatus = RecSet.Fields("claimStatus").Value
                    garageInvoiceID = RecSet.Fields("garageInvoiceID").Value
                    __date = RecSet.Fields("date").Value
                    regnr = RecSet.Fields("regnr").Value
                    vinNumber = RecSet.Fields("vinNumber").Value
                    product = RecSet.Fields("product").Value
                    brand = RecSet.Fields("brand").Value
                    model = RecSet.Fields("model").Value
                    countryCurrency = RecSet.Fields("countryCurrency").Value
                    requestedAmount = RecSet.Fields("requestedAmount").Value
                    approvedAmount = RecSet.Fields("approvedAmount").Value
                    If CDbl(claimStatus) = 0 Then
                        strClaimStatus = "Not processed"
                    ElseIf CDbl(claimStatus) = 1 Then
                        strClaimStatus = "Under processing"
                    ElseIf CDbl(claimStatus) = 2 Then
                        strClaimStatus = "Processed"
                    ElseIf CDbl(claimStatus) = 3 Then
                        strClaimStatus = "Settled"
                    End If
                    If IsDBNull(garageInvoiceID) = True Then
                        garageInvoiceID = "N/A"
                    End If
                    If rowNumber = 0 Then
                        strGrey = " bgcolor='#eeeeee'"
                        rowNumber = 1
                    Else
                        strGrey = ""
                        rowNumber = 0
                    End If
                    Response.Write("<tr" & strGrey & ">")
                    Response.Write("<td>" & strClaimStatus & "</td>")
                    Response.Write("<td>" & regnr & "</td>")
                    Response.Write("<td>" & vinNumber & "</td>")
                    Response.Write("<td>" & brand & " " & model & "</td>")
                    Response.Write("<td>" & product & "</td>")
                    Response.Write("<td align='right'>" & FormatNumber(CDbl(requestedAmount), 2) & " " & countryCurrency & "</td>")
                    Response.Write("<td align='right'>" & FormatNumber(CDbl(approvedAmount), 2) & " " & countryCurrency & "</td>")
                    Response.Write("<td>" & garageInvoiceID & "</td>")
                    Response.Write("<td>" & Left(__date, 10) & "</td>")
                    '********** the bellow VIEW DETAILS will send the user to editVehicle.aspx *************
                    Response.Write("<td><a href='editVehicle.aspx?vehicleID=" & vehicleID & "'>view details</a></td>")
                    Response.Write("</tr>")
                    RecSet.MoveNext()
                Loop
            End If
            RecSet.Close()
            RecSet = Nothing
            dbClose()
            Response.Write("<tr><td colspan='10'><hr class='hrHeadline'></td></tr>")
            Response.Write("</table>")
            Response.Write("</td>")
            Response.Write("</tr>")
        End If
        Response.Write("</table>")
        Under()
    End If
%>
